package com.lebang.common;

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import java.io.InputStream;
import java.io.OutputStream;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * @author Eliohn on 2019/8/30.
 * @version 1.0
 */
public class ExeclUtil {


    public static void write(Map<String, String> column, List<Map<String, Object>> data, OutputStream out) {
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("sheet1");
            sheet.setDefaultColumnWidth(20);
            sheet.setDefaultRowHeightInPoints(25.0F);
            HSSFRow columns = sheet.createRow(0);
            int x = 0;
            CellStyle style = wb.createCellStyle();
            for (Map.Entry col : column.entrySet()) {
                int y = 1;
                HSSFCell colName = columns.createCell(x);
                colName.setCellValue((String) col.getValue());
                HSSFRow r = null;
                for (Map row : data) {
                    if (x == 0) {
                        r = sheet.createRow(y);
                    } else {
                        r = sheet.getRow(y);
                    }
                    Object value = row.get(col.getKey());
                    HSSFCell cell = r.createCell(x);
                    if ((value instanceof Integer)) {
                        HSSFDataFormat format = wb.createDataFormat();
                        style.setDataFormat(format.getFormat("#,##0"));
                        cell.setCellStyle(style);
                        cell.setCellType(0);
                        cell.setCellValue(Double.parseDouble(value.toString()));
                    } else if ((value instanceof Double)) {
                        HSSFDataFormat format = wb.createDataFormat();
                        style.setDataFormat(format.getFormat("#,##0.00"));
                        cell.setCellStyle(style);
                        cell.setCellType(0);
                        cell.setCellValue(Double.parseDouble(value.toString()));
                    } else if ((value instanceof Date)) {
                        HSSFDataFormat format = wb.createDataFormat();
                        style.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm"));
                        cell.setCellStyle(style);
                        cell.setCellValue((Date) value);
                    } else if (null == value) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(value.toString());
                    }

                    y++;
                }
                x++;
            }
            wb.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static List<Map<String, Object>> read(InputStream inputStream) {
        try {
            List data = new ArrayList();
            Workbook workbook = null;
            workbook = new HSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);

            int rows = sheet.getLastRowNum() + 1;
            Row tmp = sheet.getRow(0);
            if (tmp == null) {
                return null;
            }
            int cols = tmp.getPhysicalNumberOfCells();

            for (int row = 1; row < rows; row++) {
                Row r = sheet.getRow(row);
                Map rowMap = new LinkedHashMap();
                for (int col = 0; col < cols; col++) {
                    if (r.getCell(col).getCellTypeEnum().equals(CellType.STRING)) {
                        rowMap.put(tmp.getCell(col).getStringCellValue(), r.getCell(col).getStringCellValue());
                    } else if (r.getCell(col).getCellTypeEnum().equals(CellType.NUMERIC)) {
                        rowMap.put(tmp.getCell(col).getStringCellValue(), Double.valueOf(r.getCell(col).getNumericCellValue()));
                    } else if (r.getCell(col).getCellTypeEnum().equals(CellType.BOOLEAN)) {
                        rowMap.put(tmp.getCell(col).getStringCellValue(), Boolean.valueOf(r.getCell(col).getBooleanCellValue()));
                    } else {
                        rowMap.put(tmp.getCell(col).getStringCellValue(), r.getCell(col).getStringCellValue());
                    }
                }
                data.add(rowMap);
            }
            return data;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

}
